<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQL 查询分析可视化</title>
    <link href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css" rel="stylesheet">
    <link href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', 'Noto Serif SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Heiti SC", "Hiragino Sans GB", Simsun, sans-serif;
            background-color: #f8fafc;
            color: #1e293b;
        }
        .hero-gradient {
            background: linear-gradient(135deg, #4f46e5 0%, #7c3aed 100%);
        }
        .card-hover:hover {
            transform: translateY(-4px);
            box-shadow: 0 20px 25px -5px rgba(0, 0, 0, 0.1), 0 10px 10px -5px rgba(0, 0, 0, 0.04);
        }
        .sql-code {
            background-color: #1e293b;
            color: #f8fafc;
            border-left: 4px solid #7c3aed;
        }
        .result-table {
            background-color: white;
            border-radius: 0.5rem;
            overflow: hidden;
        }
        .result-table th {
            background-color: #4f46e5;
            color: white;
        }
        .result-table tr:nth-child(even) {
            background-color: #f1f5f9;
        }
        .highlight {
            position: relative;
        }
        .highlight:after {
            content: '';
            position: absolute;
            left: 0;
            bottom: 0;
            width: 100%;
            height: 30%;
            background-color: rgba(124, 58, 237, 0.2);
            z-index: -1;
            transition: all 0.3s ease;
        }
        .highlight:hover:after {
            height: 100%;
        }
    </style>
</head>
<body class="min-h-screen">
    <!-- Hero Section -->
    <div class="hero-gradient text-white py-20 px-6">
        <div class="max-w-6xl mx-auto">
            <div class="flex flex-col md:flex-row items-center justify-between">
                <div class="md:w-1/2 mb-10 md:mb-0">
                    <h1 class="text-4xl md:text-5xl font-bold mb-4">SQL 查询分析可视化</h1>
                    <p class="text-xl md:text-2xl font-light mb-8">探索数据库查询的艺术与科学</p>
                    <div class="flex space-x-4">
                        <a href="#queries" class="bg-white text-indigo-600 px-6 py-3 rounded-lg font-medium hover:bg-indigo-100 transition duration-300">
                            <i class="fas fa-search mr-2"></i>查看查询
                        </a>
                        <a href="#visualization" class="bg-indigo-700 text-white px-6 py-3 rounded-lg font-medium hover:bg-indigo-800 transition duration-300">
                            <i class="fas fa-chart-pie mr-2"></i>数据可视化
                        </a>
                    </div>
                </div>
                <div class="md:w-1/2">
                    <div class="bg-white bg-opacity-10 p-6 rounded-xl backdrop-blur-sm border border-white border-opacity-20">
                        <div class="flex items-center mb-4">
                            <div class="w-3 h-3 bg-red-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-yellow-500 rounded-full mr-2"></div>
                            <div class="w-3 h-3 bg-green-500 rounded-full"></div>
                            <div class="text-sm ml-auto">SQL</div>
                        </div>
                        <pre class="text-sm font-mono overflow-x-auto"><code class="text-indigo-100">SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.job = 'CLERK';</code></pre>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Main Content -->
    <div class="max-w-6xl mx-auto px-6 py-12">
        <!-- Introduction -->
        <div class="mb-16">
            <h2 class="text-3xl font-bold mb-6 text-gray-800 flex items-center">
                <i class="fas fa-database mr-3 text-indigo-600"></i>
                数据库查询分析
            </h2>
            <div class="grid md:grid-cols-2 gap-8">
                <div>
                    <p class="text-lg text-gray-700 leading-relaxed mb-6">
                        本页面展示了针对经典员工-部门数据库的SQL查询集合，涵盖了从基础到高级的各种查询技巧。通过这些查询，您可以学习如何有效地从关系型数据库中提取和分析数据。
                    </p>
                    <div class="bg-indigo-50 p-6 rounded-lg border-l-4 border-indigo-600">
                        <h3 class="font-bold text-indigo-800 mb-2 flex items-center">
                            <i class="fas fa-lightbulb mr-2"></i>数据库结构
                        </h3>
                        <p class="text-indigo-700">
                            数据库包含两个主要表：员工表(emp)和部门表(dept)，通过部门编号(deptno)相关联。
                        </p>
                    </div>
                </div>
                <div>
                    <div class="mermaid bg-white p-6 rounded-lg shadow-sm">
                        erDiagram
                            DEPT ||--o{ EMP : contains
                            DEPT {
                                number deptno PK
                                string dname
                                string loc
                            }
                            EMP {
                                number empno PK
                                string ename
                                string job
                                number mgr FK
                                date hiredate
                                number sal
                                number comm
                                number deptno FK
                            }
                    </div>
                </div>
            </div>
        </div>

        <!-- Queries Section -->
        <div id="queries" class="mb-16">
            <h2 class="text-3xl font-bold mb-6 text-gray-800 flex items-center">
                <i class="fas fa-search mr-3 text-indigo-600"></i>
                查询集合
            </h2>
            
            <div class="grid md:grid-cols-2 gap-8">
                <!-- Query 1 -->
                <div class="card-hover bg-white rounded-xl shadow-md overflow-hidden transition duration-300">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <span class="bg-indigo-100 text-indigo-800 text-xs font-semibold px-2.5 py-0.5 rounded mr-2">基础查询</span>
                            <span class="text-gray-500 text-sm">#1</span>
                        </div>
                        <h3 class="text-xl font-bold mb-3 text-gray-800">查询20号部门的所有员工信息</h3>
                        <div class="sql-code p-4 rounded-lg mb-4">
                            <pre class="text-sm font-mono overflow-x-auto"><code>SELECT * 
FROM emp
WHERE deptno = 20;</code></pre>
                        </div>
                        <div class="flex justify-between items-center">
                            <button class="text-indigo-600 hover:text-indigo-800 text-sm font-medium flex items-center">
                                <i class="fas fa-play-circle mr-1"></i> 执行查询
                            </button>
                            <span class="text-gray-500 text-xs">简单条件查询</span>
                        </div>
                    </div>
                </div>

                <!-- Query 2 -->
                <div class="card-hover bg-white rounded-xl shadow-md overflow-hidden transition duration-300">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <span class="bg-purple-100 text-purple-800 text-xs font-semibold px-2.5 py-0.5 rounded mr-2">多表连接</span>
                            <span class="text-gray-500 text-sm">#2</span>
                        </div>
                        <h3 class="text-xl font-bold mb-3 text-gray-800">查询CLERK员工的工号、员工名和部门名</h3>
                        <div class="sql-code p-4 rounded-lg mb-4">
                            <pre class="text-sm font-mono overflow-x-auto"><code>SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno 
AND e.job = 'CLERK';</code></pre>
                        </div>
                        <div class="flex justify-between items-center">
                            <button class="text-indigo-600 hover:text-indigo-800 text-sm font-medium flex items-center">
                                <i class="fas fa-play-circle mr-1"></i> 执行查询
                            </button>
                            <span class="text-gray-500 text-xs">内连接查询</span>
                        </div>
                    </div>
                </div>

                <!-- Query 3 -->
                <div class="card-hover bg-white rounded-xl shadow-md overflow-hidden transition duration-300">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <span class="bg-yellow-100 text-yellow-800 text-xs font-semibold px-2.5 py-0.5 rounded mr-2">条件查询</span>
                            <span class="text-gray-500 text-sm">#3</span>
                        </div>
                        <h3 class="text-xl font-bold mb-3 text-gray-800">查询奖金高于工资的员工信息</h3>
                        <div class="sql-code p-4 rounded-lg mb-4">
                            <pre class="text-sm font-mono overflow-x-auto"><code>SELECT *
FROM emp e
WHERE e.comm > e.sal;</code></pre>
                        </div>
                        <div class="flex justify-between items-center">
                            <button class="text-indigo-600 hover:text-indigo-800 text-sm font-medium flex items-center">
                                <i class="fas fa-play-circle mr-1"></i> 执行查询
                            </button>
                            <span class="text-gray-500 text-xs">比较运算</span>
                        </div>
                    </div>
                </div>

                <!-- Query 24 -->
                <div class="card-hover bg-white rounded-xl shadow-md overflow-hidden transition duration-300">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <span class="bg-blue-100 text-blue-800 text-xs font-semibold px-2.5 py-0.5 rounded mr-2">子查询</span>
                            <span class="text-gray-500 text-sm">#24</span>
                        </div>
                        <h3 class="text-xl font-bold mb-3 text-gray-800">查询工资比SMITH高的所有员工信息</h3>
                        <div class="sql-code p-4 rounded-lg mb-4">
                            <pre class="text-sm font-mono overflow-x-auto"><code>SELECT *
FROM emp
WHERE sal > (SELECT sal
             FROM emp
             WHERE ename = 'SMITH');</code></pre>
                        </div>
                        <div class="flex justify-between items-center">
                            <button class="text-indigo-600 hover:text-indigo-800 text-sm font-medium flex items-center">
                                <i class="fas fa-play-circle mr-1"></i> 执行查询
                            </button>
                            <span class="text-gray-500 text-xs">标量子查询</span>
                        </div>
                    </div>
                </div>

                <!-- Query 33 -->
                <div class="card-hover bg-white rounded-xl shadow-md overflow-hidden transition duration-300">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <span class="bg-green-100 text-green-800 text-xs font-semibold px-2.5 py-0.5 rounded mr-2">聚合函数</span>
                            <span class="text-gray-500 text-sm">#33</span>
                        </div>
                        <h3 class="text-xl font-bold mb-3 text-gray-800">查询工资高于公司平均工资的所有员工</h3>
                        <div class="sql-code p-4 rounded-lg mb-4">
                            <pre class="text-sm font-mono overflow-x-auto"><code>SELECT *
FROM emp
WHERE sal > (SELECT AVG(sal)
             FROM emp);</code></pre>
                        </div>
                        <div class="flex justify-between items-center">
                            <button class="text-indigo-600 hover:text-indigo-800 text-sm font-medium flex items-center">
                                <i class="fas fa-play-circle mr-1"></i> 执行查询
                            </button>
                            <span class="text-gray-500 text-xs">聚合子查询</span>
                        </div>
                    </div>
                </div>

                <!-- Query 39 -->
                <div class="card-hover bg-white rounded-xl shadow-md overflow-hidden transition duration-300">
                    <div class="p-6">
                        <div class="flex items-center mb-4">
                            <span class="bg-red-100 text-red-800 text-xs font-semibold px-2.5 py-0.5 rounded mr-2">分组统计</span>
                            <span class="text-gray-500 text-sm">#39</span>
                        </div>
                        <h3 class="text-xl font-bold mb-3 text-gray-800">查询各部门详细信息及人数、平均工资</h3>
                        <div class="sql-code p-4 rounded-lg mb-4">
                            <pre class="text-sm font-mono overflow-x-auto"><code>SELECT COUNT(*), ROUND(AVG(e.sal)), d.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.deptno, d.dname, d.loc;</code></pre>
                        </div>
                        <div class="flex justify-between items-center">
                            <button class="text-indigo-600 hover:text-indigo-800 text-sm font-medium flex items-center">
                                <i class="fas fa-play-circle mr-1"></i> 执行查询
                            </button>
                            <span class="text-gray-500 text-xs">多表分组统计</span>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Visualization Section -->
        <div id="visualization" class="mb-16">
            <h2 class="text-3xl font-bold mb-6 text-gray-800 flex items-center">
                <i class="fas fa-chart-bar mr-3 text-indigo-600"></i>
                数据可视化
            </h2>

            <div class="grid md:grid-cols-2 gap-8">
                <div class="bg-white p-6 rounded-xl shadow-md">
                    <h3 class="text-xl font-bold mb-4 text-gray-800">部门人数分布</h3>
                    <div class="mermaid">
                        pie
                            title 部门人数占比
                            "10号部门" : 3
                            "20号部门" : 5
                            "30号部门" : 6
                    </div>
                </div>

                <div class="bg-white p-6 rounded-xl shadow-md">
                    <h3 class="text-xl font-bold mb-4 text-gray-800">工资分布分析</h3>
                    <div class="mermaid">
                        bar
                            title 各部门平均工资
                            x-axis 部门
                            y-axis 工资
                            bar "10号部门" : 2916
                            bar "20号部门" : 2175
                            bar "30号部门" : 1566
                    </div>
                </div>

                <div class="bg-white p-6 rounded-xl shadow-md md:col-span-2">
                    <h3 class="text-xl font-bold mb-4 text-gray-800">员工-部门关系图</h3>
                    <div class="mermaid">
                        graph TD
                            DEPT["部门表(dept)"]
                            EMP["员工表(emp)"]
                            D10["10号部门"] -->|管理者| E7839[("KING")]
                            D20["20号部门"] -->|管理者| E7566[("JONES")]
                            D30["30号部门"] -->|管理者| E7698[("BLAKE")]
                            
                            E7839 -->|管理| E7782[("CLARK")]
                            E7839 -->|管理| E7566
                            E7839 -->|管理| E7698
                            
                            E7566 -->|管理| E7902[("FORD")]
                            E7566 -->|管理| E7369[("SMITH")]
                            
                            E7698 -->|管理| E7499[("ALLEN")]
                            E7698 -->|管理| E7521[("WARD")]
                            E7698 -->|管理| E7654[("MARTIN")]
                            E7698 -->|管理| E7844[("TURNER")]
                            E7698 -->|管理| E7900[("JAMES")]
                    </div>
                </div>
            </div>
        </div>

        <!-- Query Results -->
        <div class="mb-16">
            <h2 class="text-3xl font-bold mb-6 text-gray-800 flex items-center">
                <i class="fas fa-table mr-3 text-indigo-600"></i>
                查询结果示例
            </h2>

            <div class="bg-white rounded-xl shadow-md overflow-hidden">
                <div class="border-b border-gray-200 px-6 py-4">
                    <h3 class="text-lg font-semibold text-gray-800">查询#2结果：CLERK员工的工号、员工名和部门名</h3>
                </div>
                <div class="p-6">
                    <div class="result-table overflow-x-auto">
                        <table class="min-w-full divide-y divide-gray-200">
                            <thead>
                                <tr>
                                    <th class="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider">工号</th>
                                    <th class="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider">员工名</th>
                                    <th class="px-6 py-3 text-left text-xs font-medium uppercase tracking-wider">部门名</th>
                                </tr>
                            </thead>
                            <tbody class="divide-y divide-gray-200">
                                <tr>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm font-medium text-gray-900">7369</td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">SMITH</td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">RESEARCH</td>
                                </tr>
                                <tr>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm font-medium text-gray-900">7876</td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">ADAMS</td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">RESEARCH</td>
                                </tr>
                                <tr>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm font-medium text-gray-900">7900</td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">JAMES</td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">SALES</td>
                                </tr>
                                <tr>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm font-medium text-gray-900">7934</td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">MILLER</td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500">ACCOUNTING</td>
                                </tr>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>

        <!-- Key Concepts -->
        <div class="mb-16">
            <h2 class="text-3xl font-bold mb-6 text-gray-800 flex items-center">
                <i class="fas fa-book mr-3 text-indigo-600"></i>
                SQL 关键概念
            </h2>

            <div class="grid md:grid-cols-3 gap-6">
                <div class="bg-white p-6 rounded-xl shadow-sm border-t-4 border-indigo-500">
                    <div class="text-indigo-600 mb-3">
                        <i class="fas fa-project-diagram text-2xl"></i>
                    </div>
                    <h3 class="text-xl font-bold mb-2 text-gray-800">JOIN 操作</h3>
                    <p class="text-gray-600">
                        JOIN用于根据两个或多个表中的列之间的关系，从这些表中查询数据。包括INNER JOIN、LEFT JOIN、RIGHT JOIN等类型。
                    </p>
                </div>

                <div class="bg-white p-6 rounded-xl shadow-sm border-t-4 border-purple-500">
                    <div class="text-purple-600 mb-3">
                        <i class="fas fa-filter text-2xl"></i>
                    </div>
                    <h3 class="text-xl font-bold mb-2 text-gray-800">WHERE 条件</h3>
                    <p class="text-gray-600">
                        WHERE子句用于过滤记录，只返回满足指定条件的记录。可以使用比较运算符、逻辑运算符和特殊运算符如LIKE、IN、BETWEEN等。
                    </p>
                </div>

                <div class="bg-white p-6 rounded-xl shadow-sm border-t-4 border-blue-500">
                    <div class="text-blue-600 mb-3">
                        <i class="fas fa-calculator text-2xl"></i>
                    </div>
                    <h3 class="text-xl font-bold mb-2 text-gray-800">聚合函数</h3>
                    <p class="text-gray-600">
                        聚合函数对一组值执行计算并返回单一的值。常用聚合函数包括COUNT()、SUM()、AVG()、MAX()和MIN()，通常与GROUP BY子句一起使用。
                    </p>
                </div>
            </div>
        </div>
    </div>

    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: true,
                htmlLabels: true,
                curve: 'basis'
            },
            er: {
                diagramPadding: 20,
                layoutDirection: 'TB'
            }
        });
    </script>
</body>
</html>